package ship.dao;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import ship.entity.usertable;
import ship.entity.orderifm;
import ship.entity.shipinformation;
import ship.entity.stafftable;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class shipDao {
	private final String URL ="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
	private final String USERNAME ="root";
	private final String PASSWORD ="111111";

//查询全部员工(0 8)
		public List<stafftable> queryallstaffs() {
			List<stafftable> staffs = new ArrayList<>();
			stafftable staff = null;
			Connection connection = null ;
			PreparedStatement pstmt = null ;
			ResultSet rs = null;
			try {
				 Class.forName("com.mysql.jdbc.Driver");
				 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
				 String sql = "select * from stafftable limit 0,8";
				  pstmt = (PreparedStatement) connection.prepareStatement(sql);
				  rs = pstmt.executeQuery();
				  while(rs.next()) {
					  int no = rs.getInt("sno");
					  String nm = rs.getString("snm");
					  String job = rs.getString("sjob");
					  String workyears = rs.getString("sworkyears");
					  String salary = rs.getString("ssalary");
					  String adrs = rs.getString("sadrs");
					  staff = new stafftable(no,nm,job,workyears,salary,adrs);
					  staffs.add(staff);
				  }
				  return staffs ;
				  
				
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
				return null ;
			} catch (SQLException e) {
				e.printStackTrace();
				return null ;
			}catch (Exception e) {
				e.printStackTrace();
				return null ;
			}
			finally {
				try {
					if(rs!=null)rs.close();
					if(pstmt!=null)pstmt.close();
					if(connection!=null)connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
//查询全部员工(8 8)
				public List<stafftable> queryallstaffstwo() {
					List<stafftable> staffs = new ArrayList<>();
					stafftable staff = null;
					Connection connection = null ;
					PreparedStatement pstmt = null ;
					ResultSet rs = null;
					try {
						 Class.forName("com.mysql.jdbc.Driver");
						 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
						 String sql = "select * from stafftable limit 8,8";
						  pstmt = (PreparedStatement) connection.prepareStatement(sql);
						  rs = pstmt.executeQuery();
						  while(rs.next()) {
							  int no = rs.getInt("sno");
							  String nm = rs.getString("snm");
							  String job = rs.getString("sjob");
							  String workyears = rs.getString("sworkyears");
							  String salary = rs.getString("ssalary");
							  String adrs = rs.getString("sadrs");
							  staff = new stafftable(no,nm,job,workyears,salary,adrs);
							  staffs.add(staff);
						  }
						  return staffs ;
						  
						
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
						return null ;
					} catch (SQLException e) {
						e.printStackTrace();
						return null ;
					}catch (Exception e) {
						e.printStackTrace();
						return null ;
					}
					finally {
						try {
							if(rs!=null)rs.close();
							if(pstmt!=null)pstmt.close();
							if(connection!=null)connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
}
//查询全部员工(16 8)
				public List<stafftable> queryallstaffsthree() {
					List<stafftable> staffs = new ArrayList<>();
					stafftable staff = null;
					Connection connection = null ;
					PreparedStatement pstmt = null ;
					ResultSet rs = null;
					try {
						 Class.forName("com.mysql.jdbc.Driver");
						 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
						 String sql = "select * from stafftable limit 16,8";
						  pstmt = (PreparedStatement) connection.prepareStatement(sql);
						  rs = pstmt.executeQuery();
						  while(rs.next()) {
							  int no = rs.getInt("sno");
							  String nm = rs.getString("snm");
							  String job = rs.getString("sjob");
							  String workyears = rs.getString("sworkyears");
							  String salary = rs.getString("ssalary");
							  String adrs = rs.getString("sadrs");
							  staff = new stafftable(no,nm,job,workyears,salary,adrs);
							  staffs.add(staff);
						  }
						  return staffs ;
						  
						
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
						return null ;
					} catch (SQLException e) {
						e.printStackTrace();
						return null ;
					}catch (Exception e) {
						e.printStackTrace();
						return null ;
					}
					finally {
						try {
							if(rs!=null)rs.close();
							if(pstmt!=null)pstmt.close();
							if(connection!=null)connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
//查询全部用户信息(0 8)
				public List<usertable> queryallusers() {
					List<usertable> users = new ArrayList<>();
					usertable user = null;
					Connection connection = null ;
					PreparedStatement pstmt = null ;
					ResultSet rs = null;
					try {
						 Class.forName("com.mysql.jdbc.Driver");
						 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
						 String sql = "select * from usertable limit 0,8";
						  pstmt = (PreparedStatement) connection.prepareStatement(sql);
						  rs = pstmt.executeQuery();
						  while(rs.next()) {
							  int id = rs.getInt("sid");
							  String name = rs.getString("sname");
							  String email = rs.getString("semail");
							  String age = rs.getString("sage");
							  String hobby = rs.getString("shobby");
							  String ct = rs.getString("sct");
							  String address = rs.getString("saddress");
							  user = new usertable(id,name,email,age,hobby,ct,address);
							  users.add(user);
						  }
						  return users ;
						  
						
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
						return null ;
					} catch (SQLException e) {
						e.printStackTrace();
						return null ;
					}catch (Exception e) {
						e.printStackTrace();
						return null ;
					}
					finally {
						try {
							if(rs!=null)rs.close();
							if(pstmt!=null)pstmt.close();
							if(connection!=null)connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
			}
      }
}
//查询全部用户信息(8 8)
public List<usertable> queryalluserstwo() {
					List<usertable> users = new ArrayList<>();
					usertable user = null;
					Connection connection = null ;
					PreparedStatement pstmt = null ;
					ResultSet rs = null;
					try {
						 Class.forName("com.mysql.jdbc.Driver");
						 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
						 String sql = "select * from usertable limit 8,8";
						  pstmt = (PreparedStatement) connection.prepareStatement(sql);
						  rs = pstmt.executeQuery();
						  while(rs.next()) {
							  int id = rs.getInt("sid");
							  String name = rs.getString("sname");
							  String email = rs.getString("semail");
							  String age = rs.getString("sage");
							  String hobby = rs.getString("shobby");
							  String ct = rs.getString("sct");
							  String address = rs.getString("saddress");
							  user = new usertable(id,name,email,age,hobby,ct,address);
							  users.add(user);
						  }
						  return users ;
						  
						
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
						return null ;
					} catch (SQLException e) {
						e.printStackTrace();
						return null ;
					}catch (Exception e) {
						e.printStackTrace();
						return null ;
					}
					finally {
						try {
							if(rs!=null)rs.close();
							if(pstmt!=null)pstmt.close();
							if(connection!=null)connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
			}
      }
}
//查询全部用户信息(16 8)
public List<usertable> queryallusersthree() {
	List<usertable> users = new ArrayList<>();
	usertable user = null;
	Connection connection = null ;
	PreparedStatement pstmt = null ;
	ResultSet rs = null;
	try {
		 Class.forName("com.mysql.jdbc.Driver");
		 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
		 String sql = "select * from usertable limit 16,8";
		  pstmt = (PreparedStatement) connection.prepareStatement(sql);
		  rs = pstmt.executeQuery();
		  while(rs.next()) {
			  int id = rs.getInt("sid");
			  String name = rs.getString("sname");
			  String email = rs.getString("semail");
			  String age = rs.getString("sage");
			  String hobby = rs.getString("shobby");
			  String ct = rs.getString("sct");
			  String address = rs.getString("saddress");
			  user = new usertable(id,name,email,age,hobby,ct,address);
			  users.add(user);
		  }
		  return users ;
		  
		
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
		return null ;
	} catch (SQLException e) {
		e.printStackTrace();
		return null ;
	}catch (Exception e) {
		e.printStackTrace();
		return null ;
	}
	finally {
		try {
			if(rs!=null)rs.close();
			if(pstmt!=null)pstmt.close();
			if(connection!=null)connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
}
}
}
//查询全部用户信息(24 8)
public List<usertable> queryallusersfour() {
	List<usertable> users = new ArrayList<>();
	usertable user = null;
	Connection connection = null ;
	PreparedStatement pstmt = null ;
	ResultSet rs = null;
	try {
		 Class.forName("com.mysql.jdbc.Driver");
		 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
		 String sql = "select * from usertable limit 24,8";
		  pstmt = (PreparedStatement) connection.prepareStatement(sql);
		  rs = pstmt.executeQuery();
		  while(rs.next()) {
			  int id = rs.getInt("sid");
			  String name = rs.getString("sname");
			  String email = rs.getString("semail");
			  String age = rs.getString("sage");
			  String hobby = rs.getString("shobby");
			  String ct = rs.getString("sct");
			  String address = rs.getString("saddress");
			  user = new usertable(id,name,email,age,hobby,ct,address);
			  users.add(user);
		  }
		  return users ;
		  
		
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
		return null ;
	} catch (SQLException e) {
		e.printStackTrace();
		return null ;
	}catch (Exception e) {
		e.printStackTrace();
		return null ;
	}
	finally {
		try {
			if(rs!=null)rs.close();
			if(pstmt!=null)pstmt.close();
			if(connection!=null)connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
}
}
}
//查询全部订单信息(0 8)
				public List<orderifm> queryallorderifms() {
					List<orderifm> ifms = new ArrayList<>();
					orderifm ifm = null;
					Connection connection = null ;
					PreparedStatement pstmt = null ;
					ResultSet rs = null;
					try {
						 Class.forName("com.mysql.jdbc.Driver");
						 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
						 String sql = "select * from orderifm limit 0,8";
						  pstmt = (PreparedStatement) connection.prepareStatement(sql);
						  rs = pstmt.executeQuery();
						  while(rs.next()) {
							  int number = rs.getInt("snumber");
							  String name = rs.getString("sname");
							  String time = rs.getString("stime");
							  String shipname = rs.getString("sshipname");
							  String shipform = rs.getString("sshipform");
							  String gettime = rs.getString("sgettime");
							  String mode = rs.getString("smode");
							  ifm = new orderifm(number,name,time,shipname,shipform,gettime,mode);
							  ifms.add(ifm);
						  }
						  return ifms ;
						  
						
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
						return null ;
					} catch (SQLException e) {
						e.printStackTrace();
						return null ;
					}catch (Exception e) {
						e.printStackTrace();
						return null ;
					}
					finally {
						try {
							if(rs!=null)rs.close();
							if(pstmt!=null)pstmt.close();
							if(connection!=null)connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
			}
      }
}
//查询全部订单信息(8 8)
				public List<orderifm> queryallorderifmstwo() {
					List<orderifm> ifms = new ArrayList<>();
					orderifm ifm = null;
					Connection connection = null ;
					PreparedStatement pstmt = null ;
					ResultSet rs = null;
					try {
						 Class.forName("com.mysql.jdbc.Driver");
						 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
						 String sql = "select * from orderifm limit 8,8";
						  pstmt = (PreparedStatement) connection.prepareStatement(sql);
						  rs = pstmt.executeQuery();
						  while(rs.next()) {
							  int number = rs.getInt("snumber");
							  String name = rs.getString("sname");
							  String time = rs.getString("stime");
							  String shipname = rs.getString("sshipname");
							  String shipform = rs.getString("sshipform");
							  String gettime = rs.getString("sgettime");
							  String mode = rs.getString("smode");
							  ifm = new orderifm(number,name,time,shipname,shipform,gettime,mode);
							  ifms.add(ifm);
						  }
						  return ifms ;
						  
						
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
						return null ;
					} catch (SQLException e) {
						e.printStackTrace();
						return null ;
					}catch (Exception e) {
						e.printStackTrace();
						return null ;
					}
					finally {
						try {
							if(rs!=null)rs.close();
							if(pstmt!=null)pstmt.close();
							if(connection!=null)connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
			}
      }
}	
//查询全部船只信息(0 8)
				public List<shipinformation> queryshipinformation() {
					List<shipinformation> informations = new ArrayList<>();
					shipinformation information = null;
					Connection connection = null ;
					PreparedStatement pstmt = null ;
					ResultSet rs = null;
					try {
						 Class.forName("com.mysql.jdbc.Driver");
						 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
						 String sql = "select * from shipinformation limit 0,8";
						  pstmt = (PreparedStatement) connection.prepareStatement(sql);
						  rs = pstmt.executeQuery();
						  while(rs.next()) {
							  int n = rs.getInt("sn");
							  String shipname = rs.getString("sshipname");
							  String shipform = rs.getString("sshipform");
							  String price = rs.getString("sprice");
							  String tonnage = rs.getString("stonnage");
							  String source = rs.getString("ssource");
							  String evaluate = rs.getString("sevaluate");
							  String advise = rs.getString("sadvise");
							  String state = rs.getString("sstate");
							  information = new shipinformation(n,shipname,shipform,price,tonnage,source,evaluate,advise,state);
							  informations.add(information);
						  }
						  return informations ;
						  
						
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
						return null ;
					} catch (SQLException e) {
						e.printStackTrace();
						return null ;
					}catch (Exception e) {
						e.printStackTrace();
						return null ;
					}
					finally {
						try {
							if(rs!=null)rs.close();
							if(pstmt!=null)pstmt.close();
							if(connection!=null)connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
			}
      }
}
//查询全部船只信息(8 8)
				public List<shipinformation> queryshipinformationtwo() {
					List<shipinformation> informations = new ArrayList<>();
					shipinformation information = null;
					Connection connection = null ;
					PreparedStatement pstmt = null ;
					ResultSet rs = null;
					try {
						 Class.forName("com.mysql.jdbc.Driver");
						 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
						 String sql = "select * from shipinformation limit 8,8";
						  pstmt = (PreparedStatement) connection.prepareStatement(sql);
						  rs = pstmt.executeQuery();
						  while(rs.next()) {
							  int n = rs.getInt("sn");
							  String shipname = rs.getString("sshipname");
							  String shipform = rs.getString("sshipform");
							  String price = rs.getString("sprice");
							  String tonnage = rs.getString("stonnage");
							  String source = rs.getString("ssource");
							  String evaluate = rs.getString("sevaluate");
							  String advise = rs.getString("sadvise");
							  String state = rs.getString("sstate");
							  information = new shipinformation(n,shipname,shipform,price,tonnage,source,evaluate,advise,state);
							  informations.add(information);
						  }
						  return informations ;
						  
						
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
						return null ;
					} catch (SQLException e) {
						e.printStackTrace();
						return null ;
					}catch (Exception e) {
						e.printStackTrace();
						return null ;
					}
					finally {
						try {
							if(rs!=null)rs.close();
							if(pstmt!=null)pstmt.close();
							if(connection!=null)connection.close();
						} catch (SQLException e) {
							e.printStackTrace();
			}
      }
}
//根据sno查询此人(员工)是否存在
		public boolean isExistbysno(int sno) {// true:此人存在  false：不存在
		return querystafftablebysno(sno)==null? false:true  ;}
//根据snm查询此人(员工)是否存在
		public boolean isExistbysnm(String snm) {// true:此人存在  false：不存在
		return querystafftablebysnm(snm)==null? false:true  ;}
//根据sid查询此人(用户)是否存在
		public boolean isExistbysid(int sid) {
		return queryusertablebysid(sid)==null? false:true  ;}
//根据sname查询此人(用户)是否存在
		public boolean isExistbysname(String sname) {
		return queryusertablebysname(sname)==null? false:true  ;}
//根据snumber查询此订单是否存在
	    public boolean isExistbysnumber(int snumber) {
		return queryorderifmbysnumber(snumber)==null? false:true  ;}
//根据sn查询此信息是否存在
		public boolean isExistbysn(int sn) {
		return queryshipinformationbysn(sn)==null? false:true  ;}
//添加员工
		   public boolean addstafftable(stafftable staff) {
				Connection connection = null ;
				PreparedStatement pstmt = null ;
				try {
					Class.forName("com.mysql.jdbc.Driver");
					 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
					 String sql = "insert into stafftable values(?,?,?,?,?,?) ";
					  pstmt = (PreparedStatement) connection.prepareStatement(sql);
					  pstmt.setInt(1,staff.getSno());
					  pstmt.setString(2,staff.getSnm());
					  pstmt.setString(3,staff.getSjob());
					  pstmt.setString(4,staff.getSworkyears());
					  pstmt.setString(5,staff.getSsalary());
					  pstmt.setString(6,staff.getSadrs());
					  pstmt.executeUpdate();
					  int count = pstmt.executeUpdate();
					  if(count>0) 
						  return true;
				      
					  else 
						  return false;
					  
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
					return false;
				} catch (SQLException e) {
					e.printStackTrace();
					return false;
				}catch (Exception e) {
					e.printStackTrace();
					return false;
				}
				finally {
					try {
						if(pstmt!=null)pstmt.close();
						if(connection!=null)connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					
				}
				
			}	
//增加订单
		   public boolean addorderifm(orderifm ifm) {
				Connection connection = null ;
				PreparedStatement pstmt = null ;
				try {
					Class.forName("com.mysql.jdbc.Driver");
					 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
					 String sql = "insert into orderifm values(?,?,?,?,?,?,?) ";
					  pstmt = (PreparedStatement) connection.prepareStatement(sql);
					  pstmt.setInt(1,ifm.getSnumber());
					  pstmt.setString(2,ifm.getSname());
					  pstmt.setString(3,ifm.getStime());
					  pstmt.setString(4,ifm.getSshipname());
					  pstmt.setString(5,ifm.getSshipform());
					  pstmt.setString(6,ifm.getSgettime());
					  pstmt.setString(7,ifm.getSmode());
					  pstmt.executeUpdate();
					  int count = pstmt.executeUpdate();
					  if(count>0) 
						  return true;
				      
					  else 
						  return false;
					  
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
					return false;
				} catch (SQLException e) {
					e.printStackTrace();
					return false;
				}catch (Exception e) {
					e.printStackTrace();
					return false;
				}
				finally {
					try {
						if(pstmt!=null)pstmt.close();
						if(connection!=null)connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					
				}
				
}
//添加用户
		   public boolean addusertable(usertable user) {
				Connection connection = null ;
				PreparedStatement pstmt = null ;
				try {
					Class.forName("com.mysql.jdbc.Driver");
					 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
					 String sql = "insert into usertable values(?,?,?,?,?,?,?) ";
					  pstmt = (PreparedStatement) connection.prepareStatement(sql);
					  pstmt.setInt(1,user.getSid());
					  pstmt.setString(2,user.getSname());
					  pstmt.setString(3,user.getSemail());
					  pstmt.setString(4,user.getSage());
					  pstmt.setString(5,user.getShobby());
					  pstmt.setString(6,user.getSct());
					  pstmt.setString(7,user.getSaddress());
					  pstmt.executeUpdate();
					  int count = pstmt.executeUpdate();
					  if(count>0) 
						  return true;
				      
					  else 
						  return false;
					  
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
					return false;
				} catch (SQLException e) {
					e.printStackTrace();
					return false;
				}catch (Exception e) {
					e.printStackTrace();
					return false;
				}
				finally {
					try {
						if(pstmt!=null)pstmt.close();
						if(connection!=null)connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					
				}
				
}
//上架船只信息
		   public boolean addshipinformation(shipinformation information) {
				Connection connection = null ;
				PreparedStatement pstmt = null ;
				try {
					Class.forName("com.mysql.jdbc.Driver");
					 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
					 String sql = "insert into shipinformation values(?,?,?,?,?,?,?,?,?) ";
					  pstmt = (PreparedStatement) connection.prepareStatement(sql);
					  pstmt.setInt(1,information.getSn());
					  pstmt.setString(2,information.getSshipname());
					  pstmt.setString(3,information.getSshipform());
					  pstmt.setString(4,information.getSprice());
					  pstmt.setString(5,information.getStonnage());
					  pstmt.setString(6,information.getSsource());
					  pstmt.setString(7,information.getSevaluate());
					  pstmt.setString(8,information.getSadvise());
					  pstmt.setString(9,information.getSstate());
					  pstmt.executeUpdate();
					  int count = pstmt.executeUpdate();
					  if(count>0) 
						  return true;
				      
					  else 
						  return false;
					  
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
					return false;
				} catch (SQLException e) {
					e.printStackTrace();
					return false;
				}catch (Exception e) {
					e.printStackTrace();
					return false;
				}
				finally {
					try {
						if(pstmt!=null)pstmt.close();
						if(connection!=null)connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
	}
}
		   
//根据sno查该员工信息
public stafftable querystafftablebysno(int sno) {
		   		   		stafftable staff = null;
		   		   		Connection connection = null ;
		   		   		PreparedStatement pstmt = null ;
		   		   		ResultSet rs = null;
		   		   		try {
		   		   			Class.forName("com.mysql.jdbc.Driver");
		   		   			 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
		   		   			 String sql = "select * from stafftable where sno=? ";
		   		   			  pstmt = (PreparedStatement) connection.prepareStatement(sql);
		   		   			  pstmt.setInt(1,sno);
		   		   			  rs = pstmt.executeQuery();
		   		   			  if(rs.next()) {
		   		   				int no = rs.getInt("sno");
		   		   				String nm = rs.getString("snm");
		   		   				String job = rs.getString("sjob");
		   		   				String workyears = rs.getString("sworkyears");
		   		   				String salary = rs.getString("ssalary");
		   		   				String adrs = rs.getString("sadrs");
		   		   				staff = new stafftable(no,nm,job,workyears,salary,adrs);			  
		   		   			  }
		   		   			    return staff ;
		   		   			  
		   		   			
		   		   		} catch (ClassNotFoundException e) {
		   		   			e.printStackTrace();
		   		   			return null ;
		   		   		} catch (SQLException e) {
		   		   			e.printStackTrace();
		   		   			return null ;
		   		   		}catch (Exception e) {
		   		   			e.printStackTrace();
		   		   			return null ;
		   		   		}
		   		   		finally {
		   		   			try {
		   		   				if(rs!=null)rs.close();
		   		   				if(pstmt!=null)pstmt.close();
		   		   				if(connection!=null)connection.close();
		   		   			} catch (SQLException e) {
		   		   				e.printStackTrace();
		   		   			}
		   		   		}
}
//根据snm查该员工信息
public stafftable querystafftablebysnm(String snm) {
  		stafftable staff = null;
  		Connection connection = null ;
  		PreparedStatement pstmt = null ;
  		ResultSet rs = null;
  		try {
  			Class.forName("com.mysql.jdbc.Driver");
  			 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
  			 String sql = "select * from stafftable where snm=? ";
  			  pstmt = (PreparedStatement) connection.prepareStatement(sql);
  			  pstmt.setString(1,snm);
  			  rs = pstmt.executeQuery();
  			  if(rs.next()) {
  				int no = rs.getInt("sno");
  				String nm = rs.getString("snm");
  				String job = rs.getString("sjob");
  				String workyears = rs.getString("sworkyears");
  				String salary = rs.getString("ssalary");
  				String adrs = rs.getString("sadrs");
  				staff = new stafftable(no,nm,job,workyears,salary,adrs);			  
  			  }
  			    return staff ;
  			  
  			
  		} catch (ClassNotFoundException e) {
  			e.printStackTrace();
  			return null ;
  		} catch (SQLException e) {
  			e.printStackTrace();
  			return null ;
  		}catch (Exception e) {
  			e.printStackTrace();
  			return null ;
  		}
  		finally {
  			try {
  				if(rs!=null)rs.close();
  				if(pstmt!=null)pstmt.close();
  				if(connection!=null)connection.close();
  			} catch (SQLException e) {
  				e.printStackTrace();
  			}
  		}
}
//根据sname查该用户信息(登录+个人信息)
		public usertable queryusertablebysname(String sname) {
				   		usertable user = null;
				   		Connection connection = null ;
				   		PreparedStatement pstmt = null ;
				   		ResultSet rs = null;
				   		try {
				   			Class.forName("com.mysql.jdbc.Driver");
				   			 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
				   			 String sql = "select * from usertable where sname=? ";
				   			  pstmt = (PreparedStatement) connection.prepareStatement(sql);
				   			  pstmt.setString(1,sname);
				   			  rs = pstmt.executeQuery();
				   			  if(rs.next()) {
				   				int id = rs.getInt("sid");
				   				String name = rs.getString("sname");
				   				String email = rs.getString("semail");
				   				String age = rs.getString("sage");
				   				String hobyy = rs.getString("shobby");
				   				String ct = rs.getString("sct");
				   				String address = rs.getString("saddress");
				   				user = new usertable(id,name,email,age,hobyy,ct,address);			  
				   			  }
				   			  return user ;
				   			  
				   			
				   		} catch (ClassNotFoundException e) {
				   			e.printStackTrace();
				   			return null ;
				   		} catch (SQLException e) {
				   			e.printStackTrace();
				   			return null ;
				   		}catch (Exception e) {
				   			e.printStackTrace();
				   			return null ;
				   		}
				   		finally {
				   			try {
				   				if(rs!=null)rs.close();
				   				if(pstmt!=null)pstmt.close();
				   				if(connection!=null)connection.close();
				   			} catch (SQLException e) {
				   				e.printStackTrace();
				   			}
				   		}
		}		
//根据sid查该用户信息
public usertable queryusertablebysid(int sid) {
		   		usertable user = null;
		   		Connection connection = null ;
		   		PreparedStatement pstmt = null ;
		   		ResultSet rs = null;
		   		try {
		   			Class.forName("com.mysql.jdbc.Driver");
		   			 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
		   			 String sql = "select * from usertable where sid=? ";
		   			  pstmt = (PreparedStatement) connection.prepareStatement(sql);
		   			  pstmt.setInt(1,sid);
		   			  rs = pstmt.executeQuery();
		   			  if(rs.next()) {
		   				int id = rs.getInt("sid");
		   				String name = rs.getString("sname");
		   				String email = rs.getString("semail");
		   				String age = rs.getString("sage");
		   				String hobyy = rs.getString("shobby");
		   				String ct = rs.getString("sct");
		   				String address = rs.getString("saddress");
		   				user = new usertable(id,name,email,age,hobyy,ct,address);			  
		   			  }
		   			  return user ;
		   			  
		   			
		   		} catch (ClassNotFoundException e) {
		   			e.printStackTrace();
		   			return null ;
		   		} catch (SQLException e) {
		   			e.printStackTrace();
		   			return null ;
		   		}catch (Exception e) {
		   			e.printStackTrace();
		   			return null ;
		   		}
		   		finally {
		   			try {
		   				if(rs!=null)rs.close();
		   				if(pstmt!=null)pstmt.close();
		   				if(connection!=null)connection.close();
		   			} catch (SQLException e) {
		   				e.printStackTrace();
		   			}
		   		}
}
//根据snumber查询该订单信息
public orderifm queryorderifmbysnumber(int snumber) {
		   		orderifm ifm = null;
		   		Connection connection = null ;
		   		PreparedStatement pstmt = null ;
		   		ResultSet rs = null;
		   		try {
		   			Class.forName("com.mysql.jdbc.Driver");
		   			 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
		   			 String sql = "select * from orderifm where snumber=? ";
		   			  pstmt = (PreparedStatement) connection.prepareStatement(sql);
		   			  pstmt.setInt(1,snumber);
		   			  rs = pstmt.executeQuery();
		   			  if(rs.next()) {
		   				int number = rs.getInt("snumber");
		   				String person = rs.getString("sname");
		   				String time = rs.getString("stime");
		   			    String shipname = rs.getString("sshipname");
		   		        String shipform = rs.getString("sshipform");
		   	            String gettime = rs.getString("sgettime");
		                String mode = rs.getString("smode");
		   				ifm = new orderifm(number,person,time,shipname,shipform,gettime,mode);			  
		   			  }
		   			    return ifm ;
		   		} catch (ClassNotFoundException e) {
		   			e.printStackTrace();
		   			return null ;
		   		} catch (SQLException e) {
		   			e.printStackTrace();
		   			return null ;
		   		}catch (Exception e) {
		   			e.printStackTrace();
		   			return null ;
		   		}
		   		finally {
		   			try {
		   				if(rs!=null)rs.close();
		   				if(pstmt!=null)pstmt.close();
		   				if(connection!=null)connection.close();
		   			} catch (SQLException e) {
		   				e.printStackTrace();
		   			}
		   		}
}
//根据sname查询订单信息（个人）
public List<orderifm> queryorderifmbysname(String sname) {
	            List<orderifm> ifms = new ArrayList<>();
	            orderifm ifm = null;
		   		Connection connection = null ;
		   		PreparedStatement pstmt = null ;
		   		ResultSet rs = null;
		   		try {
		   			Class.forName("com.mysql.jdbc.Driver");
		   			 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
		   			 String sql = "select * from orderifm where sname=? ";
		   			  pstmt = (PreparedStatement) connection.prepareStatement(sql);
		   			  pstmt.setString(1, sname);;
		   			  rs = pstmt.executeQuery();
					  while(rs.next()) {
						  int number = rs.getInt("snumber");
						  String name = rs.getString("sname");
						  String time = rs.getString("stime");
						  String shipname = rs.getString("sshipname");
						  String shipform = rs.getString("sshipform");
						  String gettime = rs.getString("sgettime");
						  String mode = rs.getString("smode");
						  ifm = new orderifm(number,name,time,shipname,shipform,gettime,mode);
						  ifms.add(ifm);
					  }
					  return ifms ;
		   		} catch (ClassNotFoundException e) {
		   			e.printStackTrace();
		   			return null ;
		   		} catch (SQLException e) {
		   			e.printStackTrace();
		   			return null ;
		   		}catch (Exception e) {
		   			e.printStackTrace();
		   			return null ;
		   		}
		   		finally {
		   			try {
		   				if(rs!=null)rs.close();
		   				if(pstmt!=null)pstmt.close();
		   				if(connection!=null)connection.close();
		   			} catch (SQLException e) {
		   				e.printStackTrace();
		   			}
		   		}
}
//根据sn查询该船只信息
public shipinformation queryshipinformationbysn(int sn) {
	            shipinformation information = null;
		   		Connection connection = null ;
		   		PreparedStatement pstmt = null ;
		   		ResultSet rs = null;
		   		try {
		   			Class.forName("com.mysql.jdbc.Driver");
		   			 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
		   			 String sql = "select * from shipinformation where sn=? ";
		   			  pstmt = (PreparedStatement) connection.prepareStatement(sql);
		   			  pstmt.setInt(1,sn);
		   			  rs = pstmt.executeQuery();
		   			  if(rs.next()) {
		   				int n = rs.getInt("sn");
		   				String shipname = rs.getString("sshipname");
		   				String shipform = rs.getString("sshipform");
		   				String price = rs.getString("sprice");
		   				String tonnage = rs.getString("stonnage");
		   				String source = rs.getString("ssource");
		   				String evaluate = rs.getString("sevaluate");
		   				String advise = rs.getString("sadvise");
		   				String state = rs.getString("sstate");
		   				information = new shipinformation(n,shipname,shipform,price,tonnage,source,evaluate,advise,state);			  
		   			  }
		   			    return information ;
		   			  
		   			
		   		} catch (ClassNotFoundException e) {
		   			e.printStackTrace();
		   			return null ;
		   		} catch (SQLException e) {
		   			e.printStackTrace();
		   			return null ;
		   		}catch (Exception e) {
		   			e.printStackTrace();
		   			return null ;
		   		}
		   		finally {
		   			try {
		   				if(rs!=null)rs.close();
		   				if(pstmt!=null)pstmt.close();
		   				if(connection!=null)connection.close();
		   			} catch (SQLException e) {
		   				e.printStackTrace();
		   			}
		   		}
}
//根据sno修改员工信息
	public boolean updatestafftable(int sno,stafftable staff) {
		Connection connection = null ;
		PreparedStatement pstmt = null ;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
			 String sql = "update stafftable set snm =?,sjob =?,sworkyears=?,ssalary =?,sadrs =? where sno=? ";
			  pstmt = (PreparedStatement) connection.prepareStatement(sql);
			  pstmt.setString(1,staff.getSnm());
			  pstmt.setString(2,staff.getSjob());
			  pstmt.setString(3,staff.getSworkyears());
			  pstmt.setString(4,staff.getSsalary());
			  pstmt.setString(5,staff.getSadrs());
			  //修改的那个人
			  pstmt.setInt(6,staff.getSno());
			  int count = pstmt.executeUpdate();
			  if(count>0) 
				  return true;
		      
			  else 
				  return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		finally {
			try {
				if(pstmt!=null)pstmt.close();
				if(connection!=null)connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
}
}
//根据sid修改用户信息
	public boolean updateusertable(int sid,usertable user) {
		Connection connection = null ;
		PreparedStatement pstmt = null ;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
			 String sql = "update usertable set sname =?,semail =?,sage=?,shobby =?,sct =?,saddress=? where sid=? ";
			  pstmt = (PreparedStatement) connection.prepareStatement(sql);
			  pstmt.setString(1,user.getSname());
			  pstmt.setString(2,user.getSemail());
			  pstmt.setString(3,user.getSage());
			  pstmt.setString(4,user.getShobby());
			  pstmt.setString(5,user.getSct());
			  pstmt.setString(6,user.getSaddress());
			  //修改的那个人
			  pstmt.setInt(7,user.getSid());
			  int count = pstmt.executeUpdate();
			  if(count>0) 
				  return true;
		      
			  else 
				  return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		finally {
			try {
				if(pstmt!=null)pstmt.close();
				if(connection!=null)connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
   }
}
//根据snumber修改订单信息
		public boolean updateorderifm(int snumber,orderifm ifm) {
			Connection connection = null ;
			PreparedStatement pstmt = null ;
			try {
				Class.forName("com.mysql.jdbc.Driver");
				 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
				 String sql = "update orderifm set sname =?,stime =?,sshipname=?,sshipform =?,sgettime =?,smode=? where snumber=? ";
				  pstmt = (PreparedStatement) connection.prepareStatement(sql);
				  pstmt.setString(1,ifm.getSname());
				  pstmt.setString(2,ifm.getStime());
				  pstmt.setString(3,ifm.getSshipname());
				  pstmt.setString(4,ifm.getSshipform());
				  pstmt.setString(5,ifm.getSgettime());
				  pstmt.setString(6,ifm.getSmode());
				  //修改的那个人
				  pstmt.setInt(7,ifm.getSnumber());
				  int count = pstmt.executeUpdate();
				  if(count>0) 
					  return true;
			      
				  else 
					  return false;
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
				return false;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}catch (Exception e) {
				e.printStackTrace();
				return false;
			}
			finally {
				try {
					if(pstmt!=null)pstmt.close();
					if(connection!=null)connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
	   }
	}
//根据sn修改船只信息
		public boolean updateshipinformation(int sn,shipinformation information) {
			Connection connection = null ;
			PreparedStatement pstmt = null ;
			try {
				Class.forName("com.mysql.jdbc.Driver");
				 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
				 String sql = "update shipinformation set sshipname =?,sshipform =?,sprice=?,stonnage =?,ssource =?,sevaluate =?,sadvise =?,sstate =? where sn=? ";
				  pstmt = (PreparedStatement) connection.prepareStatement(sql);
				  pstmt.setString(1,information.getSshipname());
				  pstmt.setString(2,information.getSshipform());
				  pstmt.setString(3,information.getSprice());
				  pstmt.setString(4,information.getStonnage());
				  pstmt.setString(5,information.getSsource());
				  pstmt.setString(6,information.getSevaluate());
				  pstmt.setString(7,information.getSadvise());
				  pstmt.setString(8,information.getSstate());
				  //修改的那个人
				  pstmt.setInt(9,information.getSn());
				  int count = pstmt.executeUpdate();
				  if(count>0) 
					  return true;
			      
				  else 
					  return false;
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
				return false;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}catch (Exception e) {
				e.printStackTrace();
				return false;
			}
			finally {
				try {
					if(pstmt!=null)pstmt.close();
					if(connection!=null)connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
	}
}
//根据sno删除员工信息
			public boolean deletestafftablebysid(int sno) {
				Connection connection = null ;
				PreparedStatement pstmt = null ;
				try {
					Class.forName("com.mysql.jdbc.Driver");
					 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
					 String sql = "delete from stafftable where sno=?";
					  pstmt = (PreparedStatement) connection.prepareStatement(sql);
					  pstmt.setInt(1,sno);
					  pstmt.executeUpdate();
					  int count = pstmt.executeUpdate();
					  if(count>0) 
						  return true;
					  else 
						  return false;
					  
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
					return false;
				} catch (SQLException e) {
					e.printStackTrace();
					return false;
				}catch (Exception e) {
					e.printStackTrace();
					return false;
				}
				finally {
					try {
						if(pstmt!=null)pstmt.close();
						if(connection!=null)connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					
		}
	}
//根据sid删除用户信息
		public boolean deleteusertablebysid(int sid) {
			Connection connection = null ;
			PreparedStatement pstmt = null ;
			try {
				Class.forName("com.mysql.jdbc.Driver");
				 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
				 String sql = "delete from usertable where sid=?";
				  pstmt = (PreparedStatement) connection.prepareStatement(sql);
				  pstmt.setInt(1,sid);
				  pstmt.executeUpdate();
				  int count = pstmt.executeUpdate();
				  if(count>0) 
					  return true;
				  else 
					  return false;
				  
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
				return false;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}catch (Exception e) {
				e.printStackTrace();
				return false;
			}
			finally {
				try {
					if(pstmt!=null)pstmt.close();
					if(connection!=null)connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
	}
}
//根据snumber删除订单信息
		public boolean deleteorderifmbysnumber(int snumber) {
			Connection connection = null ;
			PreparedStatement pstmt = null ;
			try {
				Class.forName("com.mysql.jdbc.Driver");
				 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
				 String sql = "delete from orderifm where snumber=?";
				  pstmt = (PreparedStatement) connection.prepareStatement(sql);
				  pstmt.setInt(1,snumber);
				  pstmt.executeUpdate();
				  int count = pstmt.executeUpdate();
				  if(count>0) 
					  return true;
				  else 
					  return false;
				  
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
				return false;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}catch (Exception e) {
				e.printStackTrace();
				return false;
			}
			finally {
				try {
					if(pstmt!=null)pstmt.close();
					if(connection!=null)connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
	}
}
//根据sn下架该船只信息
		public boolean deleteshipinformationbysn(int sn) {
			Connection connection = null ;
			PreparedStatement pstmt = null ;
			try {
				Class.forName("com.mysql.jdbc.Driver");
				 connection = (Connection) DriverManager.getConnection( URL,USERNAME,PASSWORD );
				 String sql = "delete from shipinformation where sn=?";
				  pstmt = (PreparedStatement) connection.prepareStatement(sql);
				  pstmt.setInt(1,sn);
				  pstmt.executeUpdate();
				  int count = pstmt.executeUpdate();
				  if(count>0) 
					  return true;
				  else 
					  return false;
				  
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
				return false;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}catch (Exception e) {
				e.printStackTrace();
				return false;
			}
			finally {
				try {
					if(pstmt!=null)pstmt.close();
					if(connection!=null)connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
	}
}
}